package com.boc.ewr.business.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.boc.ewr.business.entity.EwrReceiptRatio;
import com.boc.ewr.business.entity.bo.EwrReceiptRatioBO;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Description: 智慧e对-对账指标考核
 * @Author: jeecg-boot
 * @Date:   2023-06-08
 * @Version: V1.0
 */
public interface EwrReceiptRatioMapper extends BaseMapper<EwrReceiptRatio> {

    //获取重点账单回执数
    @Select("select dp.DEPART_LEADER as parentBranchNm, ca.ORG_BRANCH_NO as branchNo, dp.DEPART_NAME as branchNm, COUNT(ca.ID) as keyBillReceipt from CA_RTN_LIST ca LEFT JOIN CUSTOM_DEPART dp ON ca.ORG_BRANCH_NO = dp.ID " +
            "where PROTOCOL_TYPE in ( '7', '4', '5', '6') GROUP BY dp.DEPART_LEADER, ca.ORG_BRANCH_NO, dp.DEPART_NAME ORDER BY dp.DEPART_LEADER, ca.ORG_BRANCH_NO")
    List<EwrReceiptRatioBO> creatKeyInfo();
    //重点账单发送数
    @Select("select dp.DEPART_LEADER as parentBranchNm, ca.OBRANCH_NO as branchNo, dp.DEPART_NAME as branchNm, COUNT(ca.ID) as keyBillSend from ca_bal_dtl ca LEFT JOIN CUSTOM_DEPART dp ON ca.OBRANCH_NO = dp.ID " +
            "where PROTOCOL_TYPE in ( '7', '4', '5', '6') GROUP BY dp.DEPART_LEADER, ca.OBRANCH_NO, dp.DEPART_NAME ORDER BY dp.DEPART_LEADER, ca.OBRANCH_NO")
    List<EwrReceiptRatioBO> createKeyTotalInfo();

    //获取非重点账单回执数
    @Select("select dp.DEPART_LEADER as parentBranchNm, ca.ORG_BRANCH_NO as branchNo, dp.DEPART_NAME as branchNm, COUNT(ca.ID) as nokeyBillReceipt " +
            "from CA_RTN_LIST ca LEFT JOIN CUSTOM_DEPART dp ON ca.ORG_BRANCH_NO = dp.ID " +
            "where PROTOCOL_TYPE in ('0', '3') GROUP BY dp.DEPART_LEADER, ca.ORG_BRANCH_NO, dp.DEPART_NAME ORDER BY dp.DEPART_LEADER, ca.ORG_BRANCH_NO")
    List<EwrReceiptRatioBO> createNoKeyInfo();

    //非重点账单发送数
    @Select("select dp.DEPART_LEADER as parentBranchNm, ca.OBRANCH_NO as branchNo, dp.DEPART_NAME as branchNm, COUNT(ca.ID) as nokeyBillSend from ca_bal_dtl ca LEFT JOIN CUSTOM_DEPART dp ON ca.OBRANCH_NO = dp.ID " +
            "where PROTOCOL_TYPE in ('0', '3') GROUP BY dp.DEPART_LEADER, ca.OBRANCH_NO, dp.DEPART_NAME ORDER BY dp.DEPART_LEADER, ca.OBRANCH_NO")
    List<EwrReceiptRatioBO> createNoKeyTotalInfo();

    //网银账单发送数（重点+非重点）
    @Select("select dp.DEPART_LEADER as parentBranchNm, ca.OBRANCH_NO as branchNo, dp.DEPART_NAME as branchNm, COUNT(ca.ID) as ebankBillSend from ca_bal_dtl ca LEFT JOIN CUSTOM_DEPART dp ON ca.OBRANCH_NO = dp.ID  " +
            "where ca.RTN_CHANNEL = '1' GROUP BY dp.DEPART_LEADER, ca.OBRANCH_NO, dp.DEPART_NAME ORDER BY dp.DEPART_LEADER, ca.OBRANCH_NO")
    List<EwrReceiptRatioBO> createEbankTotalInfo();

    //网银回执总数（重点+非重点）
    @Select("select dp.DEPART_LEADER as parentBranchNm, ca.ORG_BRANCH_NO as branchNo, dp.DEPART_NAME as branchNm, COUNT(ca.ID) as ebankBillReceipt " +
            "from CA_RTN_LIST ca LEFT JOIN CUSTOM_DEPART dp ON ca.ORG_BRANCH_NO = dp.ID " +
            "where ca.RTN_CHANNEL = '1' GROUP BY dp.DEPART_LEADER, ca.ORG_BRANCH_NO, dp.DEPART_NAME ORDER BY dp.DEPART_LEADER, ca.ORG_BRANCH_NO")
    List<EwrReceiptRatioBO> createEbankInfo();

    //计算分行总数
    @Select("select ra.PARENT_BRANCH_NM, ra.CA_MON, SUM(TOTAL_BILL_SEND) as TOTAL_BILL_SEND, SUM(KEY_BILL_SEND) as KEY_BILL_SEND, SUM(NOKEY_BILL_SEND) as NOKEY_BILL_SEND, SUM(EBANK_BILL_SEND) as EBANK_BILL_SEND, SUM(KEY_BILL_RECEIPT) as KEY_BILL_RECEIPT, SUM(NOKEY_BILL_RECEIPT) as NOKEY_BILL_RECEIPT, SUM(EBANK_BILL_RECEIPT) as EBANK_BILL_RECEIPT from ewr_receipt_ratio ra where PARENT_BRANCH_NM is NOT NULL GROUP BY ra.PARENT_BRANCH_NM, ra.CA_MON")
    List<EwrReceiptRatioBO> createSumInfo();
}
